package com.anhry.app.safety.util;

import java.lang.reflect.Method;
import java.util.List;

import javax.servlet.http.HttpServletResponse;

import org.apache.poi.hssf.usermodel.HSSFCellStyle;
import org.apache.poi.hssf.usermodel.HSSFDataFormat;
import org.apache.poi.hssf.usermodel.HSSFFont;
import org.apache.poi.hssf.usermodel.HSSFRow;
import org.apache.poi.hssf.usermodel.HSSFSheet;
import org.apache.poi.hssf.usermodel.HSSFWorkbook;
import org.apache.poi.hssf.util.Region;

import com.anhry.app.safety.util.bean.ExcelHeaderBean;



public class ExcelServiceExtends extends ExcelServiceAbstract {
	
	public HSSFWorkbook ExcelExportByArray(List<Object[]> htmlList,List<Object[]> sumlist,HttpServletResponse response,ExcelHeaderBean[][] bean,String title,int cell0,Integer...is) throws Exception {
		HSSFWorkbook wb = new HSSFWorkbook();
		HSSFSheet sheet = wb.createSheet();
		HSSFCellStyle centerStyle = wb.createCellStyle();// 设置为水平居中
		centerStyle.setAlignment(HSSFCellStyle.ALIGN_CENTER);
		centerStyle.setVerticalAlignment(HSSFCellStyle.VERTICAL_CENTER);
		
		if(htmlList.size() == cell0){//首列是否合并
			sheet.addMergedRegion(new Region(bean.length+1,(short)0,bean.length+htmlList.size(),(short)0));  //Region(int rowFrom, short colFrom, int rowTo, short colTo) 	
		}else if(cell0 != -1){
			int tag = htmlList.size()/cell0 ;
			for(int i=0;i<tag;i++){
				sheet.addMergedRegion(new Region(bean.length+1+i*cell0,(short)0,bean.length+(i+1)*cell0,(short)0));  //Region(int rowFrom, short colFrom, int rowTo, short colTo) 	
			}
		}
		int width = 1 ;//正常显示列的长度
		if (htmlList.size() > 0) {
			for (int i = 0; i < htmlList.size(); i++) {	// 按要求创建各单元格
				HSSFRow row = sheet.createRow(i+bean.length+1);
				Object []obj = (Object[]) htmlList.get(i) ;
				int s=0;
				for(int j=0;j<obj.length;j++){
					boolean bool = true ;
					if(is!=null){
						for(int h:is){
							if(h==j) {bool = false ; break;}
						}
					}
					if(bool){
						super.createCell(row,s,obj[j],centerStyle);
						s++;
					}
				}
				width = s ;
			}
		}
		HSSFFont font = wb.createFont();		
	    font.setFontName("Arial Narrow");
	    font.setBoldweight(HSSFFont.BOLDWEIGHT_BOLD);
		font.setFontHeightInPoints((short) 12);
		HSSFCellStyle centerStyle1 = wb.createCellStyle(); //设置样式
		centerStyle1.setBorderRight(HSSFCellStyle.BORDER_MEDIUM);  // 设置边框
		centerStyle1.setFont(font);// 设置字体
		centerStyle1.setAlignment(HSSFCellStyle.ALIGN_CENTER);
		centerStyle1.setVerticalAlignment(HSSFCellStyle.VERTICAL_CENTER);
		this.initHeader(sheet,centerStyle1,bean,title,width);// 初始化头部为水平居中
		this.initEnd(sheet,centerStyle,sumlist,htmlList.size()+bean.length+1,width);// 初始化头部为水平居中
		response.setContentType("application/ms-excel;charset=UTF-8");
		response.setHeader("Content-disposition", "attachment; filename=" + new String(title.getBytes("GBK"), "ISO-8859-1") + ".xls");
		return wb;
	}
	
	public HSSFWorkbook ExcelExportIrregular(List<Object[]> htmlList,List<Object[]> sumlist,HttpServletResponse response,ExcelHeaderBean[][] bean,int rowIndex1,int rowIndex2,String title,int cell0,Integer...is) throws Exception {
		HSSFWorkbook wb = new HSSFWorkbook();
		HSSFSheet sheet = wb.createSheet();
		HSSFCellStyle centerStyle = wb.createCellStyle();// 设置为水平居中
		centerStyle.setAlignment(HSSFCellStyle.ALIGN_CENTER);
		centerStyle.setVerticalAlignment(HSSFCellStyle.VERTICAL_CENTER);
		
		if(htmlList.size() == cell0){//首列是否合并
			sheet.addMergedRegion(new Region(bean.length+1,(short)0,bean.length+htmlList.size(),(short)0));  //Region(int rowFrom, short colFrom, int rowTo, short colTo) 	
		}else if(cell0 != -1){
			int tag = htmlList.size()/cell0 ;
			for(int i=0;i<tag;i++){
				sheet.addMergedRegion(new Region(bean.length+1+i*cell0,(short)0,bean.length+(i+1)*cell0,(short)0));  //Region(int rowFrom, short colFrom, int rowTo, short colTo) 	
			}
		}
		
		int k = rowIndex1 - 1;	//定义要合并列的列号，即从那一列开始合并
		while(k < rowIndex2){	//合并数据相同的列
			int count = 0;
			int begin = -1;
			int end = -1;
			for(int i=1,len=htmlList.size();i<len;i++){
				Object[] frontObj = htmlList.get(i-1);
				Object[] behindObj = htmlList.get(i);
				String frontCell = (String) frontObj[k];
				String behindCell = (String) behindObj[k]; 
				if(behindCell.equals(frontCell)){
					if(count == 0){
						begin = i+1;
					}
					count ++;
					if(i==len-1 && begin!=-1){
						end = begin + count;
					}
				}else{
					if(begin != -1){
						end = begin + count;
					}
					count = 0;
				}
				if(begin!=-1 && end!=-1){
					sheet.addMergedRegion(new Region(begin,(short)k,end,(short)k));
					begin = -1;
					end = -1;
				}
			}
			k++;
		}
		int width = 1 ;//正常显示列的长度
		if (htmlList.size() > 0) {
			for (int i = 0; i < htmlList.size(); i++) {	// 按要求创建各单元格
				HSSFRow row = sheet.createRow(i+bean.length+1);
				Object []obj = (Object[]) htmlList.get(i) ;
				int s=0;
				for(int j=0;j<obj.length;j++){
					boolean bool = true ;
					if(is!=null){
						for(int h:is){
							if(h==j) {bool = false ; break;}
						}
					}
					if(bool){
						super.createCell(row,s,obj[j],centerStyle);
						s++;
					}
				}
				width = s ;
			}
		}
		HSSFFont font = wb.createFont();		
	    font.setFontName("Arial Narrow");
	    font.setBoldweight(HSSFFont.BOLDWEIGHT_BOLD);
		font.setFontHeightInPoints((short) 12);
		HSSFCellStyle centerStyle1 = wb.createCellStyle(); //设置样式
		centerStyle1.setBorderRight(HSSFCellStyle.BORDER_MEDIUM);  // 设置边框
		centerStyle1.setFont(font);// 设置字体
		centerStyle1.setAlignment(HSSFCellStyle.ALIGN_CENTER);
		centerStyle1.setVerticalAlignment(HSSFCellStyle.VERTICAL_CENTER);
		this.initHeader(sheet,centerStyle1,bean,title,width);
		this.initEnd(sheet,centerStyle,sumlist,htmlList.size()+bean.length+1,width);// 初始化头部为水平居中
		response.setContentType("application/ms-excel;charset=UTF-8");
		response.setHeader("Content-disposition", "attachment; filename=" + new String(title.getBytes("GBK"), "ISO-8859-1") + ".xls");
		return wb;
	}
	
	public HSSFWorkbook ExcelExportByPojo(List htmlList,List sumList,HttpServletResponse response,ExcelHeaderBean[][] bean,String title,int cell0,Class clazz,String[] fieldArray) throws Exception {
		HSSFWorkbook wb = new HSSFWorkbook();
		HSSFSheet sheet = wb.createSheet();
		HSSFCellStyle centerStyle = wb.createCellStyle();// 设置为水平居中
		centerStyle.setAlignment(HSSFCellStyle.ALIGN_CENTER);
		centerStyle.setVerticalAlignment(HSSFCellStyle.VERTICAL_CENTER);
		HSSFCellStyle centerStyle1 = wb.createCellStyle();// 设置为水平居中的日期样式
		centerStyle1.setAlignment(HSSFCellStyle.ALIGN_CENTER);
		centerStyle1.setVerticalAlignment(HSSFCellStyle.VERTICAL_CENTER);
		centerStyle1.setDataFormat(HSSFDataFormat.getBuiltinFormat("yyyy-MM-dd"));
		if(htmlList.size() == cell0){//首列是否合并
			sheet.addMergedRegion(new Region(bean.length+1,(short)0,bean.length+htmlList.size(),(short)0));  //Region(int rowFrom, short colFrom, int rowTo, short colTo) 	
		}else if(cell0 != -1){
			int tag = htmlList.size()/cell0 ;
			for(int i=0;i<tag;i++){
				sheet.addMergedRegion(new Region(bean.length+1+i*cell0,(short)0,bean.length+(i+1)*cell0,(short)0));  //Region(int rowFrom, short colFrom, int rowTo, short colTo) 	
			}
		}
		for(int i=0;i<htmlList.size();i++){
			HSSFRow row = sheet.createRow(i+bean.length+1);
			Object obj = htmlList.get(i) ;
			for(int j=0;j<fieldArray.length;j++){
				String methodName = "get"+fieldArray[j].substring(0, 1).toUpperCase()+fieldArray[j].substring(1);
				Method method = clazz.getMethod(methodName, null) ;
				Object value = method.invoke(obj, null) ;
				if(method.getReturnType().toString().indexOf("Date") !=-1 ){ //日期类型
					super.createCell(row,j,value,centerStyle1);
				}else{
					super.createCell(row,j,value,centerStyle);
				}
			}
		}
		HSSFFont font = wb.createFont();		
	    font.setFontName("Arial Narrow");
	    font.setBoldweight(HSSFFont.BOLDWEIGHT_BOLD);
		font.setFontHeightInPoints((short) 12);
		HSSFCellStyle centerStyle2 = wb.createCellStyle(); //设置样式
		centerStyle2.setBorderRight(HSSFCellStyle.BORDER_MEDIUM);  // 设置边框
		centerStyle2.setFont(font);// 设置字体
		centerStyle2.setAlignment(HSSFCellStyle.ALIGN_CENTER);
		centerStyle2.setVerticalAlignment(HSSFCellStyle.VERTICAL_CENTER);
		this.initHeader(sheet,centerStyle2,bean,title,fieldArray.length);// 初始化头部为水平居中
		if(sumList!=null){
			this.initEnd(sheet,centerStyle,sumList,htmlList.size()+bean.length+1,fieldArray.length);// 初始化尾部为水平居中
		}
		response.setContentType("application/ms-excel;charset=UTF-8");
		response.setHeader("Content-disposition", "attachment; filename=" + new String(title.getBytes("GBK"), "ISO-8859-1") + ".xls");
		return wb ;
	}

}
